/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package xmlasm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.apache.struts2.ServletActionContext;
import utils.DBUtils;
import xmlasm.action.FeedAction;
import xmlasm.dto.Feeds;
import xmlasm.dto.Feed;

/**
 *
 * @author Hoang
 */
public class FeedDAO {

    public static Feeds getAllFeeds() {
        PreparedStatement stm = null;
        ResultSet rs1 = null;
        Connection con = null;

        try {
            con = DBUtils.makeConnection();
            String sql = "SELECT * FROM FEED";
            stm = con.prepareStatement(sql);
            rs1 = stm.executeQuery();
            Feeds fs = new Feeds();
            while (rs1.next()) {
                int feedId = rs1.getInt("feedId");
                String url = rs1.getString("url");
                String title = rs1.getString("title");
                String description = rs1.getString("description");
                Date date = rs1.getTimestamp("addedDate");
                Feed f = new Feed(feedId, url, title, description, date);
                //lay list user-------
                ResultSet rs2 = null;
                sql = "SELECT userId FROM [USER-FEED] WHERE feedId=" + String.valueOf(feedId);
                PreparedStatement stm2 = con.prepareStatement(sql);
                rs2 = stm2.executeQuery();
                Feed.Userlist ul = new Feed.Userlist();
                List<Integer> userList = new ArrayList<Integer>();
                while (rs2.next()) {
                    int userid = rs2.getInt("userId");
                    userList.add(userid);
                }
                ul.setUserId(userList);
                f.setUserlist(ul);
                rs2.close();
                stm2.close();
                //--------------------
                fs.getFeed().add(f);
            }
            return fs;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (rs1 != null) {
                    rs1.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", ex.getMessage());
            }
        }
        return null;
    }

    public static Feeds getFeedsByUserId(int id) {
        PreparedStatement stm = null;
        ResultSet rs = null;
        Connection con = null;
        Feeds fs = new Feeds();
        try {
            con = DBUtils.makeConnection();
            String sql = "SELECT f.* "
                    + "FROM [FEED] f, (SELECT uf.feedId FROM [USER-FEED] uf WHERE uf.userId=?) sub"
                    + " WHERE f.feedId=sub.feedId";
            stm = con.prepareStatement(sql);
            stm.setInt(1, id);
            rs = stm.executeQuery();
            List<Feed> temp = new ArrayList<Feed>();
            while (rs.next()) {
                int feedId = rs.getInt("feedId");
                String url = rs.getString("url");
                String title = rs.getString("title");
                String description = rs.getString("description");
                Date date = rs.getDate("addedDate");
                Feed f = new Feed(feedId, url, title, description, date);
                fs.getFeed().add(f);
            }
            return fs;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return null;
    }

    //Khi user add 1 feed da co san thi dung ham nay
    public static boolean setAssociation(int userID, int feedID) {
        PreparedStatement stm = null;
        Connection con = null;

        try {
            con = DBUtils.makeConnection();
            String sql = "INSERT INTO [USER-FEED](userId,feedId) VALUES(?,?)";
            stm = con.prepareStatement(sql);
            stm.setInt(1, userID);
            stm.setInt(2, feedID);
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return false;
    }

    //Khi user remove feed ra khoi feedlist thi dung ham nay
    public static boolean removeAssociation(int userID, int feedID) {
        PreparedStatement stm = null;
        Connection con = null;

        try {
            con = DBUtils.makeConnection();
            String sql = "DELETE FROM [USER-FEED] WHERE userId=? AND feedId=?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, userID);
            stm.setInt(2, feedID);
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return false;
    }

    //Khi admin insert feed thi dung ham nay (Nho tra ra gia tri da add)
    public static int insertFeed(FeedAction f) {
        PreparedStatement stm = null;
        Connection con = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "INSERT INTO FEED(url, title, [description], addedDate) VALUES(?,?,?,?) SELECT @@IDENTITY AS feedId";
            stm = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            stm.setString(1, f.getRssLink());
            stm.setString(2, f.getRssName());
            stm.setString(3, f.getRssDescription());
            stm.setTimestamp(4, new java.sql.Timestamp(new Date().getTime()));
            int result = stm.executeUpdate();
            if (result > 0) {
                rs = stm.getGeneratedKeys();
                if (rs.next()) {
                    int feedId = rs.getInt("feedId");
                    return feedId;
                }
            }
            return -1;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    stm.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return -1;
    }

    //Khi admin delete feed khoi database thi dung ham nay
    public static boolean deleteFeed(int feedId) {
        PreparedStatement stm = null;
        Connection con = null;

        try {
            con = DBUtils.makeConnection();
            String sql = "DELETE FROM FEED WHERE feedId=?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, feedId);
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return false;
    }

    public static boolean updateFeed(Feed f) {
        PreparedStatement stm = null;
        Connection con = null;
        try {
            con = DBUtils.makeConnection();
            String sql = "UPDATE [FEED] SET title=?, description=? WHERE feedId=?";
            stm = con.prepareStatement(sql);
            stm.setString(1, f.getTitle());
            stm.setString(2, f.getDecription());
            stm.setInt(3, f.getFeedId());
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return false;
    }

    public static boolean removeAssociation(int feedId) {
        PreparedStatement stm = null;
        Connection con = null;

        try {
            con = DBUtils.makeConnection();
            String sql = "DELETE FROM [USER-FEED] WHERE feedId=?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, feedId);
            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            HttpServletRequest request = ServletActionContext.getRequest();
            request.setAttribute("SQLErrors", ex.getMessage());
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("SQLErrors", e.getMessage());
            }
        }
        return false;
    }
}
